How to: Import an Excel spreadsheet to Access.
Solution:
Select 'Get External Data' from the 'File' menu and select 'Import...', locate the spreadsheet to import, click 'Import', and then follow the instructions displayed by the Import Spreadsheet Wizard.
NOTE: Data from a Microsoft Excel (versions 2.x, 3.0, 4.0, 5.0, and 7.0) spreadsheet can be imported to Access. However, the data in the spreadsheet must be arranged in an appropriate tabular format, and the spreadsheet must have the same type of data in each column (field) and the same fields in every row (record).
1) If the destination database (the database to which to import a spreadsheet) is not already open, open the database.
2) If the Database window is not active, activate the Database window.
3) Select the 'File' menu and select 'Get External Data'. (A submenu appears.)
4) Select 'Import...' from the submenu. (The Import dialog box appears.)
5) Select 'Microsoft Excel (*.xls)' from the 'Files of type' drop-down list.
6) Select the drive where the spreadsheet is located from the 'Look in' drop-down list.
7) Select the folder that contains the spreadsheet from the 'Look in' list box. (The spreadsheet file is displayed.)
NOTE: If the file is in a subfolder, continue selecting folders until the desired file is displayed.
8) Select the spreadsheet file. (The file is highlighted.)
9) Click 'Import'. (The Import Spreadsheet Wizard appears.)
Importing an Excel spreadsheet
10) If the spreadsheet has a database password:
a) Type the password in the 'Password' box.
b) Click 'OK'.
11) Do one of the following:
the Import Spreadsheet Wizard
a) Select the 'Show Worksheets' radio button to show all the worksheets in the spreadsheet.
NOTE: Access 7.0 shows multiple worksheets ONLY for Excel 5.0 and 7.0 files. For Excel 4.0 spreadsheet files that contain multiple worksheets, save each worksheet as an individual file before importing.
b) Select the 'Show named ranges' radio button to show all the named ranges in the spreadsheet.
12) Select the worksheet or the range to import.
NOTE: Only one worksheet or range can be imported at a time.
13) Click 'Next'.
14) To import the first row of the worksheet data as field names, select the 'First Row Contains Column Headings' check box.
the First Row Contains Column Headings check box
NOTE: If some data in the first row cannot be used as an Access field name, Access displays a message box. Click 'OK' to have the Wizard automatically assign valid field names for the data.
the Message box
15) Click 'Next'.
16) (Optional) Customize fields in the worksheet:
a) Select a field from the list box at the bottom of the 'Import Spreadsheet Wizard' dialog box. (The entire field or column is highlighted.)
b) Do one or both of the following in the 'Field Options' group:
1] Change the name of the field in the 'Field Name' box.
2] Select one of the following indexing options from the 'Indexed' drop-down list:
a] Select 'No' to not index the field.
b] Select 'Yes (Duplicates OK)' to index the field and allow duplicates.
c] Select 'Yes (No duplicates)' to index the field without duplicates.
c) Select the 'Do not import field (Skip)' check box to not import the selected field.
Customizing fields
17) Click 'Next'.
18) Do one of the following:
a) Select the 'Let Access add Primary Key' radio button to have Access set the primary key for the imported worksheet.
b) Select the 'Choose my own Primary Key' radio button, and select the field to be used as the primary key from the drop-down list next to the radio button.
c) Select the 'No Primary Key' radio button to not set any primary key.
Defining a primary key
19) Click 'Next'.
20) Type the name of the table to which the worksheet is to be imported in the 'Import to Table' box.
the Import to Table box
21) Click 'Finish' to start the import process. (A message appears when the import is complete.)
22) Click 'OK' to close the Import Spreadsheet Wizard.
23) Make sure the data types or other field properties assigned by Access are correct:
24) (Optional) Repeat steps 3) through 23) to import another worksheet.
